<?php

namespace App\Models;

use App\Http\Controllers\Admin\CommonController;
use App\Http\Controllers\Controller;
use Exception;
use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Support\Facades\DB;

/**
 * 新书、馆藏书订单表
 */
class BookHomeOrder extends BaseModel
{
    use HasFactory;

    const CREATED_AT = null;
    const UPDATED_AT = null;

    protected $table = 'book_home_order';

    /**
     * 获取新书条形码
     * 起点值 ：01079300000001
     * @param $is_first  是否第一次获取   true 是  false  不是
     */
    public function getBarcode($max_barcode = null, $is_first = true)
    {
        if (empty($max_barcode) && !$is_first) {
            throw new Exception('条形码获取失败');
        }

        //获取新书最大一个条形码
        if (empty($max_barcode)) {
            $BookHomePurchaseObj = new BookHomePurchase();
            $max_barcode = $BookHomePurchaseObj->where('type', 1)
                ->where('barcode', 'like', '010793%')
                ->lock(true)
                ->max('barcode', false);

            if (empty($max_barcode)) {
                $max_barcode = '01079300000000';
            }
        }

        $controllerObj = new Controller();
        $libApi = $controllerObj->getLibApiObj();
        for ($i = 1; $i < 100; $i++) {
            $max_barcode = substr($max_barcode, 5);
            $max_barcode = $max_barcode + $i;
            $max_barcode = '01079' . $max_barcode;

            //验证当前条形码在系统是否存在
            $barcodeExists = $libApi->getAssetByBarcode($max_barcode);
            if ($barcodeExists['code'] === 202) {
                return $max_barcode;
            } else
                //如果是第一条，并且接口报错
                if ($barcodeExists['code'] === 201 && $is_first) {
                    throw new \Exception('接口数据获取失败'); //第一个失败就全部失败
                }
        }
        throw new \Exception('条形码获取有误，请联系管理员处理'); //获取10次都获取不到，就不在获取
    }

    /**
     * 收货地址 （关联）
     */
    public function getOrderAddress()
    {
        return $this->hasOne(BookHomeOrderAddress::class, 'order_id', 'id');
    }
    /**
     * 新书订单书籍信息 （关联）
     */
    public function getOrderBookInfo()
    {
        return $this->belongsToMany(ShopBook::class, BookHomeOrderBook::class, 'order_id', 'book_id');
        //   return $this->belongsToMany('App\Models\ShopBook', 'book_home_order_book', 'order_id', '');
    }

    /**
     * 馆藏书订单书籍信息 （关联）
     */
    public function getLibOrderBookInfo()
    {
        return $this->belongsToMany(LibBook::class, BookHomeOrderBook::class, 'order_id', 'book_id');
    }

    /**
     * 馆藏书订单书籍 与  条形码 信息 （关联）
     */
    public function getLibBarcodeInfo()
    {
        return $this->belongsToMany(LibBookBarcode::class, BookHomeOrderBook::class, 'order_id', 'barcode_id');
    }

    /**
     * 判断 邮费是否还够支付本次订单
     * @param price  本次邮费
     */
    public function getPostageBudget($price, $account_id)
    {
        $bookHomePurchaseSetModelObj = new BookHomePurchaseSet();
        // $purchase_budget = $bookHomePurchaseSetModelObj->where('type' , 14)->value('number');
        $purchase_budget = $bookHomePurchaseSetModelObj->where('type', 14)->first();
        if ($purchase_budget) {
            $purchase_budget = $purchase_budget->toArray();
        }

        if ($purchase_budget['way'] == 2) {
            $start_month = $purchase_budget['start_month'];
            $month_number = $purchase_budget['month_number'];
            //获取开始月份和结束月份
            list($start_month, $end_month) = $this->getTimeByStartMonthAndMonthNumber($start_month, $month_number);

            $start_time = $purchase_budget['start_time'];
            $end_time = $purchase_budget['end_time'];
            $start_time = $start_time < 10 ? '0' . (int)$start_time : $start_time;
            $end_time = $end_time < 10 ? '0' . (int)$end_time : $end_time;
            $purchase_budget['start_time'] = $start_month . '-' . $start_time . ' 00:00:00';
            $purchase_budget['end_time'] = $end_month . '-' . $end_time . ' 23:59:59';
        }

        if (empty($purchase_budget['times']) || $purchase_budget['start_time'] > date('Y-m-d H:i:s') || $purchase_budget['end_time'] < date('Y-m-d H:i:s')) {
            return false; //不在有效期
        }
        //查看这段时间系统扣减的次数
        $pay_number = self::where('create_time', '>', $purchase_budget['start_time'])
            ->where('create_time', '<', $purchase_budget['end_time'])
            ->where('payment', 2)
            ->where('account_id', $account_id)
            ->whereRaw('is_pay = 1 || is_pay = 2 || is_pay = 6 || is_pay = 8')
            ->count();
        if ($purchase_budget['times'] <= $pay_number) {
            return false; //次数过多
        }

        $already_purchase_money = $bookHomePurchaseSetModelObj->getPostageMoney();
        if (($purchase_budget['number'] - $already_purchase_money) < $price) {
            return false;
        }
        return true;
    }

    /**
     * 获取开始月份，结束月份
     * @param $start_month
     * @param $month_number
     */
    public function getTimeByStartMonthAndMonthNumber($start_month, $month_number)
    {
        if ($start_month > date('Y-m')) {
            return [$start_month, date('Y-m', strtotime("+" . $month_number . " month" ,strtotime($start_month)))];
        }
        $number = get_month_num($start_month . '-01', date('Y-m-01'));
        // $number = $number + 1;//因为算出来的是相差月份，现在需要加上当月
        $number = ($number % $month_number);
        $start_time = date('Y-m', strtotime("-" . $number . " month"));
        return [$start_time, date('Y-m')];
    }


    /**
     * 组装处理数据
     * @param $res
     */
    public function checkOrderData($res)
    {
        $data = [];

        $data = $this->getReaderInfo($res, $data);

        $data['shop_info']['shop_name'] = isset($res['shop_name']) ? $res['shop_name'] : null;

        $data['order_info']['id'] = $res['id'];
        $data['order_info']['type'] = $res['type'];
        $data['order_info']['is_pay'] = $res['is_pay'];
        $data['order_info']['order_number'] = $res['order_number'];
        $data['order_info']['create_time'] = $res['create_time'];
        $data['order_info']['payment'] = $res['payment'] == 1 ? "微信支付" : '其他';
        $data['order_info']['price'] = $res['price'];
        $data['order_info']['dis_price'] = $res['dis_price'];
        $data['order_info']['change_time'] = $res['change_time'];
        $data['order_info']['agree_time'] = $res['agree_time'];
        $data['order_info']['deliver_time'] = $res['deliver_time'];
        $data['order_info']['refund_number'] = $res['refund_number'];
        $data['order_info']['refund_time'] = $res['refund_time'];
        $data['order_info']['refund_remark'] = $res['refund_remark'];
        $data['order_info']['tracking_number'] = $res['tracking_number'];

        $data['address_info']['username'] = $res['address_username'];
        $data['address_info']['tel'] = $res['address_tel'];
        $data['address_info']['province'] = $res['province'];
        $data['address_info']['city'] = $res['city'];
        $data['address_info']['district'] = $res['district'];
        $data['address_info']['street'] = $res['street'];
        $data['address_info']['address'] = $res['address'];

        return $data;
    }

    /**
     * 组装处理数据
     * @param $res
     */
    public function checkBookHomePurchaseData($res)
    {
        $data = [];

        $data = $this->getReaderInfo($res, $data);

        $data['shop_info']['shop_name'] = $res['shop_name'];


        $data['book_info']['book_name'] = $res['book_name'];
        $data['book_info']['author'] = $res['author'];
        $data['book_info']['isbn'] = $res['isbn'];
        $data['book_info']['press'] = $res['press'];
        $data['book_info']['price'] = $res['price'];
        $data['book_info']['pre_time'] = $res['pre_time'];
        $data['book_info']['book_selector'] = $res['book_selector'];

        $data['BookHomePurchase_info']['create_time'] = $res['create_time'];
        $data['BookHomePurchase_info']['settle_state'] = $res['settle_state'];
        $data['BookHomePurchase_info']['expire_time'] = $res['expire_time'];
        $data['BookHomePurchase_info']['overdue_money'] = $res['overdue_money'];
        $data['BookHomePurchase_info']['is_drop'] = $res['is_drop'];
        $data['BookHomePurchase_info']['drop_money'] = $res['drop_money'];
        $data['BookHomePurchase_info']['is_pay'] = $res['is_pay'];
        $data['BookHomePurchase_info']['return_time'] = $res['return_time'];
        $data['BookHomePurchase_info']['return_state'] = $res['return_state'];
        $data['BookHomePurchase_info']['settle_affirm_time'] = $res['settle_affirm_time'];
        $data['BookHomePurchase_info']['settle_sponsor_time'] = $res['settle_sponsor_time'];
        $data['BookHomePurchase_info']['pay_time'] = $res['pay_time'];
        $data['BookHomePurchase_info']['settle_lib_manage_name'] = $res['settle_lib_manage_name'];
        $data['BookHomePurchase_info']['settle_shop_manage_name'] = $res['settle_shop_manage_name'];
        $data['BookHomePurchase_info']['pur_manage_name'] = $res['pur_manage_name'];
        $data['BookHomePurchase_info']['return_manage_name'] = $res['return_manage_name'];
        $data['BookHomePurchase_info']['money_manage_name'] = $res['money_manage_name'];
        $data['BookHomePurchase_info']['overdue_ratio'] = $res['overdue_ratio'];
        $data['BookHomePurchase_info']['drop_ratio'] = $res['drop_ratio'];

        return $data;
    }

    /**
     * 组装读者信息
     * @param $res
     * @param $data
     * @return array
     */
    public function getReaderInfo($res, $data = [])
    {
        is_object($res) && $res = $res->toArray();

        //读者证号信息
        $data['reader_info']['account'] = $res['account'];
        $data['reader_info']['username'] = $res['username'];
        $data['reader_info']['register_time'] = array_key_exists('register_time', $res) ? $res['register_time'] : (!empty($res['time']) ? $res['time'] : '');
        $data['reader_info']['end_time'] = !empty($res['end_time']) ? $res['end_time'] : '';
        $data['reader_info']['status_card'] = !empty($res['status_card']) ? $res['status_card'] : ''; //读者证状态
        $data['reader_info']['sex'] = $res['sex'] == 1 ? '男' : '女';
        $data['reader_info']['id_card'] = !empty($res['id_card']) ? substr_replace($res['id_card'], '********', 6, 8) : '';;
        $data['reader_info']['tel'] = !empty($res['tel']) ? $res['tel'] : '';
        $data['reader_info']['diff_money'] = !empty($res['diff_money']) ? $res['diff_money'] : '';
        $data['reader_info']['recom_diff_money'] = !empty($res['recom_diff_money']) ? $res['recom_diff_money'] : '';

        //获取 年借阅本书
        if (!empty($res['s_time']) && $res['e_time'] > date('Y-m-d H:i:s')) {
            $data['reader_info']['recomed_num'] = $res['recomed_num']; //年借阅本书
            $data['reader_info']['recomed_money'] = $res['recomed_money']; //年借阅金额
        } else {
            $data['reader_info']['recomed_num'] = 0; //年借阅本书
            $data['reader_info']['recomed_money'] = '0.00'; //年借阅金额
        }
        //获取月借阅本书
        if (!empty($res['last_mmm']) && $res['last_mmm'] == date('Ym')) {
            $data['reader_info']['last_num'] = $res['last_num']; //本月采购本书
            $data['reader_info']['last_money'] = $res['last_money']; //本月采购金额
        } else {
            $data['reader_info']['last_num'] = 0; //本月采购本书
            $data['reader_info']['last_money'] = '0.00'; //本月采购金额
        }
        $data['reader_info']['purch_num_lib'] = $res['purch_num_lib']; //图书馆总采购本书
        $data['reader_info']['purch_num_shop'] = $res['purch_num_shop']; //书店总采购本书
        $data['reader_info']['purch_money_shop'] = $res['purch_money_shop']; //书店总采购金额

        $data['reader_info']['lib_name'] = config('other.lib_name'); //图书馆名字

        return $data;
    }

    /**
     * 查看有多少条新申请的订单
     * @param $type 类型 1 新书（默认）   2 馆藏书
     * @param $shop_ids 管辖的书店id
     * @param $time 在这个时间之后新增的数据，时间戳
     */
    public function getBookHomeOrderNumber($type, $shop_ids = null, $time = null)
    {
        return $this->where('type', $type)
            ->where('is_pay', 2)
            ->where(function ($query) use ($shop_ids, $time) {
                if (!empty($shop_ids)) {
                    $query->whereIn('shop_id', $shop_ids);
                }
                if (!empty($time)) {
                    $query->where('create_time', '>',  date('Y-m-d H:i:s', $time));
                }
            })
            ->count();
    }


    /**
     * 获取未支付的订单，判断是否过期，修改状态
     */
    public static function checkNoPayOrder($order_id = null)
    {
        $BookHomeOrderModel = new BookHomeOrder();
        $BookHomeOrderBookModel = new BookHomeOrderBook();
        $res = $BookHomeOrderModel->where('is_pay', 1)->where('expire_time', '>', date('Y-m-d H:i:s'))->where(function ($query) use ($order_id) {
            if ($order_id) {
                $query->where('id', $order_id);
            }
        })->get();
        foreach ($res as $key => $val) {
            // $change_time = date('Y-m-d H:i:s' , strtotime( "+30 minute" , strtotime($val['create_time'])));
            // if($change_time <= date('Y-m-d H:i:s')){
            $BookHomeOrderModel->where('id', $val['id'])->update([
                'is_pay' => 4,
                'change_time' => $val['expire_time'],
            ]);
            //    }

            $book_id_all = $BookHomeOrderBookModel->where('order_id', $val['id'])->pluck('book_id');
            //增加库存
            if ($val['type'] == 1) {
                foreach ($book_id_all as $v) {
                    $res = ShopBook::modifyRepertoryNumber($v, 1);
                    if ($res !== true) {
                        throw new \Exception($res);
                    }
                }
            }
        }
    }


    /**
     * 申请列表、发货列表、所有订单列表
     * @param type  类型 1 新书  2 馆藏书
     * @param is_pay  类型  2 已支付  5 .已退款  6.已同意（后台单纯同意）
     *              7 已拒绝（后台单纯同意）  8已发货（数据就增加到用户采购列表）
     *              9 无法发货   固定参数 申请列表 2   发货列表 6  所有订单列表 5789
     * @param start_time 申请开始时间   数据格式    2020-05-12 12:00:00
     * @param end_time 申请结束时间     数据格式    2020-05-12 12:00:00
     * @param keywords_type 检索key  0 全部 1 订单号 2 读者证号 3 收件人姓名  4 收件人电话
     * @param keywords 检索值
     * @param shop_id 书店id   不传  或转空  或 0，表示全部
     * @param shop_all_id  管辖书店限制
     * @param limit  条数 默认 10 条
     */
    public function lists($field = null, $type = null, $is_pay = null, $shop_id = null, $keywords = null, $keywords_type = null, $start_time = null, $end_time = null, $shop_all_id = null, $limit = 10)
    {
        if (empty($field)) {
            $field = [
                'n.id', 'n.order_number', 'n.tracking_number', 'n.create_time', 'n.is_pay', 'n.deliver_time', 'n.agree_time', 'n.refund_time', 'n.change_time',
                'r.id as account_id', 'r.account', 'r.username', 's.name as shop_name', 'd.username as address_username', 'd.tel', 'd.province', 'd.city',
                'd.district', 'd.street', 'd.address', 'refund_remark'
            ];
        }
        //  DB::enableQueryLog();
        $res = $this->from($this->getTable() . ' as n')
            ->select($field)
            ->join("user_account_lib as r", 'n.account_id', '=', 'r.id')
            ->leftJoin("shop as s", 's.id', '=', 'n.shop_id')
            ->join("book_home_order_address as d", 'd.order_id', '=', 'n.id')
            ->where(function ($query) use ($keywords_type, $keywords) {
                if ($keywords) {
                    if ($keywords_type) {
                        switch ($keywords_type) {
                            case 1:
                                $query->where('n.order_number', 'like', "%$keywords%");
                                break;
                            case 2:
                                $query->where('r.account', 'like', "%$keywords%");
                                break;
                            case 3:
                                $query->where('d.username', 'like', "%$keywords%");
                                break;
                            case 4:
                                $query->where('d.tel', 'like', "%$keywords%");
                                break;
                        }
                    } else {
                        $query->orWhere('n.order_number', 'like', "%$keywords%")
                            ->orWhere('r.account', 'like', "%$keywords%")
                            ->orWhere('d.username', 'like', "%$keywords%")
                            ->orWhere('d.tel', 'like', "%$keywords%");
                    }
                }
            })
            ->where(function ($query) use ($is_pay, $start_time, $end_time, $shop_id, $shop_all_id) {
                if (!empty($start_time) && !empty($end_time)) {
                    $query->whereBetween('n.create_time', [$start_time, $end_time]);
                }
                if (!empty($is_pay)) {
                    $is_pay = str_split($is_pay);
                    $query->whereIn('n.is_pay', $is_pay);
                }
                if (!empty($shop_id)) {
                    $query->where('n.shop_id', $shop_id);
                }
                if (!empty($shop_all_id)) {
                    $query->whereIn('n.shop_id', $shop_all_id);
                }
            })
            //   ->where('s.is_del', 1)
            ->where('n.type', $type)
            ->orderByDesc('n.create_time')
            ->paginate($limit)
            ->toArray();

        //   dump(DB::getQueryLog());die;
        return $res;
    }

    /**
     * 详情
     * @param order_id  订单id
     */
    public function detail($order_id, $field = null)
    {
        if (empty($field)) {
            $field = [
                'n.id',
                'n.type',
                'n.order_number',
                'n.tracking_number',
                'n.refund_number',
                'n.refund_remark',
                'n.create_time',
                'n.payment',
                'n.price',
                'n.dis_price',
                'n.discount',
                'n.is_pay',
                'n.deliver_time',
                'n.agree_time',
                'n.refund_time',
                'n.change_time',
                'r.id as account_id',
                'r.account',
                'r.username',
                'r.account',
                'r.status_card',
                'r.time as register_time',
                'r.diff_money',
                'r.recom_diff_money',
                'r.end_time',
                'r.username',
                'r.sex',
                'r.id_card',
                'r.tel',
                'r.recomed_num',
                'r.recomed_money',
                'r.s_time',
                'r.e_time',
                'r.last_mmm',
                'r.last_num',
                'r.last_money',
                'r.purch_num_lib',
                'r.purch_money_shop',
                'r.purch_num_shop',
                's.name as shop_name',
                'd.username as address_username',
                'd.tel as address_tel',
                'd.province',
                'd.city',
                'd.district',
                'd.street',
                'd.address',
            ];
        }
        $res = $this->from($this->getTable() . ' as n')
            ->select($field)
            ->join("user_account_lib as r", 'n.account_id', '=', 'r.id')
            ->leftjoin("shop as s", 's.id', '=', 'n.shop_id')
            ->join("book_home_order_address as d", 'd.order_id', '=', 'n.id')
            ->where('n.id', $order_id)
            ->first();
        return $res;
    }


    /**
     * 获取对应订单数量
     * @param type 类型id 1 新书  2馆藏书
     * @param is_pay 状态
     * @param shop_id 书店id
     * @param shop_all_id 管辖书店数组id
     * @param user_id 用户id
     */
    public function getOrderNumber($type, $is_pay, $shop_id = null, $shop_all_id = null, $user_id = null, $start_time = null, $end_time = null)
    {
        $res = $this->where(function ($query) use ($type, $is_pay, $shop_id, $shop_all_id, $user_id, $start_time, $end_time) {
            if (!empty($is_pay)) {
                $is_pay = !is_array($is_pay) ? explode(',', $is_pay) : $is_pay;
                $query->whereIn('is_pay', $is_pay);
            }
            if (!empty($shop_all_id)) {
                $query->whereIn('shop_id', $shop_all_id);
            }
            if (!empty($type)) {
                $query->where('type', $type);
            }
            if (!empty($shop_id)) {
                $query->where('shop_id', $shop_id);
            }
            if (!empty($user_id)) {
                $query->where('user_id', $user_id);
            }
            if ($start_time && $end_time) {
                $query->whereBetween('create_time', [$start_time, $end_time]);
            }
        })->count();
        return $res;
    }

    /**
     * 获取订单下的所有书籍信息
     * @param $order_id 订单号
     */
    public function getNewBookInfoByOrderId($order_id)
    {
        $res = $this->from($this->getTable() . ' as o')
            ->select('b.*')
            ->join('shop_book as b', 'o.book_id', '=', 'b.id')
            ->where('o.id', $order_id)
            ->get();
        return $res;
    }



    /**
     * 申请退款列表
     * @param start_time 申请开始时间   数据格式    2020-05-12 12:00:00
     * @param end_time 申请结束时间     数据格式    2020-05-12 12:00:00
     * @param keywords_type  检索条件   0 全部 1 订单号 2 读者证号 3 读者证号姓名
     * @param keywords  检索条件
     * @param shop_id 书店id   不传  或转空  或 0，表示全部
     * @param type	  类型 0、全部 1 新书（默认）   2 馆藏书 （如果type为2，shop_id无效，前端变为不可选状态）
     * @param page  页数默认为 1
     * @param limit  条数 默认 10 条
     */
    public function refundList($params)
    {
        $field = $params['field'] ?? null;
        $keywords = $params['keywords'] ?? null;
        $keywords_type = $params['keywords_type'] ?? null;
        $start_time = $params['start_time'] ?? null;
        $end_time = $params['end_time'] ?? null;
        $shop_id = $params['shop_id'] ?? null;
        $shop_all_id = $params['shop_all_id'] ?? null;
        $type = $params['type'] ?? 0;
        $limit = $params['limit'] ?? 10;
        $page = $params['page'] ?? 10;
        if (empty($field)) {
            $field = ['n.id', 'n.order_number', 'n.create_time', 'n.is_pay', 'n.type', 'r.account', 'r.username', 's.name as shop_name', 'd.username as address_username', 'd.tel', 'd.province', 'd.city', 'd.district', 'd.street', 'd.address', 'n.agree_time', 'n.deliver_time', 'n.refund_time', 'n.refund_remark', 'n.refund_manage_id'];
        }

        //     DB::enableQueryLog();
        $res = $this->from($this->getTable() . ' as n')
            ->select($field)
            ->join("user_account_lib as r", 'n.account_id', '=', 'r.id')
            ->leftJoin("shop as s", 's.id', '=', 'n.shop_id')
            ->join("book_home_order_address as d", 'd.order_id', '=', 'n.id')
            ->where(function ($query) use ($keywords_type, $keywords) {
                if ($keywords) {
                    if ($keywords_type) {
                        switch ($keywords_type) {
                            case 1:
                                $query->where('n.order_number', 'like', "%$keywords%");
                                break;
                            case 2:
                                $query->where('r.account', 'like', "%$keywords%");
                                break;
                            case 3:
                                $query->where('r.username', 'like', "%$keywords%");
                                break;
                            case 4:
                                $query->where('d.tel', 'like', "%$keywords%");
                                break;
                        }
                    } else {
                        $query->orWhere('n.order_number', 'like', "%$keywords%")
                            ->orWhere('r.account', 'like', "%$keywords%")
                            ->orWhere('r.username', 'like', "%$keywords%")
                            ->orWhere('d.tel', 'like', "%$keywords%");
                    }
                }
            })

            ->where(function ($query) use ($type,  $shop_id, $shop_all_id) {
                if (empty($type)) {
                    if (!empty($shop_id)) {
                        $query->where("n.shop_id", $shop_id)->whereIn('n.shop_id', $shop_all_id);
                    } elseif (!empty($shop_all_id)) {
                        //  $query->whereRaw(DB::raw('n.shop_id in ' . $shop_all_id . ' or n.shop_id is null'));
                        $query->whereIn('n.shop_id', $shop_all_id)->orWhereNull('n.shop_id');
                    } else {
                        $query->whereNull('n.shop_id');
                    }
                } elseif ($type == 1) {
                    if (!empty($shop_id)) {
                        $query->where("n.shop_id", $shop_id)->whereIn('n.shop_id', $shop_all_id);
                    }
                    $query->where("n.type", 1);
                } else {
                    $query->where("n.type", 2);
                }
            })
            ->where(function ($query) use ($start_time, $end_time) {
                if (!empty($start_time) && !empty($end_time)) {
                    $query->whereBetween('n.create_time', [$start_time, $end_time]);
                }
            })
            ->where(function ($query) {
                $query->whereIn('is_pay', [5, 7, 9]);
            })
            ->where('dis_price', '<>', 0) //去掉图书馆支付的金额，因为图书馆实际未支付
            ->orderByDesc('n.is_pay')
            ->orderByDesc('n.create_time')
            ->paginate($limit)
            ->toArray();
        // dump(DB::getQueryLog());
        // die;
        if ($res['data']) {
            //获取书籍信息
            $bookHomeOrderBookModel = new BookHomeOrderBook();
            $newBookModel = new ShopBook();
            $libBookModel = new LibBook();
            foreach ($res['data'] as $key => $val) {
                //读者证号信息
                $res['data'][$key]['reader_info']['account'] = $val['account'];
                $res['data'][$key]['reader_info']['username'] = $val['username'];
                unset($res['data'][$key]['account'], $res['data'][$key]['username']);

                $res['data'][$key]['shop_info']['shop_name'] = $val['shop_name'];
                unset($res['data'][$key]['shop_name']);

                $res['data'][$key]['order_info']['id'] = $val['id'];
                $res['data'][$key]['order_info']['is_pay'] = $val['is_pay'];
                $res['data'][$key]['order_info']['type'] = $val['type'];
                $res['data'][$key]['order_info']['order_number'] = $val['order_number'];
                $res['data'][$key]['order_info']['create_time'] = $val['create_time'];
                $res['data'][$key]['order_info']['agree_time'] = $val['agree_time'];
                $res['data'][$key]['order_info']['deliver_time'] = $val['deliver_time'];
                $res['data'][$key]['order_info']['refund_time'] = $val['refund_time'];
                $res['data'][$key]['order_info']['refund_remark'] = $val['refund_remark'];
                $res['data'][$key]['order_info']['refund_manage_name'] = !empty($val['refund_manage_id']) ? Manage::getManageNameByManageId($val['refund_manage_id']) : '';
                unset($res['data'][$key]['is_pay'], $res['data'][$key]['order_number'], $res['data'][$key]['create_time'], $res['data'][$key]['id']);
                unset($res['data'][$key]['agree_time'], $res['data'][$key]['deliver_time'], $res['data'][$key]['refund_time'], $res['data'][$key]['refund_remark'], $res['data'][$key]['refund_manage_id']);

                $res['data'][$key]['address_info']['username'] = $val['address_username'];
                $res['data'][$key]['address_info']['tel'] = $val['tel'];
                $res['data'][$key]['address_info']['province'] = $val['province'];
                $res['data'][$key]['address_info']['city'] = $val['city'];
                $res['data'][$key]['address_info']['district'] = $val['district'];
                $res['data'][$key]['address_info']['street'] = $val['street'];
                $res['data'][$key]['address_info']['address'] = $val['address'];
                unset($res['data'][$key]['address_username']);
                unset($res['data'][$key]['tel']);
                unset($res['data'][$key]['province']);
                unset($res['data'][$key]['city']);
                unset($res['data'][$key]['district']);
                unset($res['data'][$key]['street']);
                unset($res['data'][$key]['address']);

                if ($val['type'] == 1) {
                    $book_id = $bookHomeOrderBookModel->where('order_id', $val['id'])->pluck('book_id')->toArray();
                    $res['data'][$key]['book_info'] = $newBookModel->select('book_name', 'author', 'press', 'pre_time', 'isbn', 'price', 'book_selector')
                        ->whereIn('id', $book_id)
                        ->get();
                } else {
                    $barcode_id = $bookHomeOrderBookModel->where('order_id', $val['id'])->pluck('barcode_id')->toArray();
                    $res['data'][$key]['book_info'] = $libBookModel->from($libBookModel->getTable() . 'as l')
                        ->select('l.book_name', 'l.author', 'l.press', 'l.pre_time', 'l.isbn', 'l.price', 'b.barcode')
                        ->join('lib_book_barcode as b', 'b.book_id', '=', 'l.id')
                        ->whereIn('b.id', $barcode_id)
                        ->get();
                }

                $commonControllerObj = new CommonController();
                $res['data'][$key][$commonControllerObj->list_index_key] = $commonControllerObj->addSerialNumber($page, $limit);
                $res = $commonControllerObj->disPageData($res);
            }
        }
        return $res;
    }




    /**
     *（发起方） 邮费统计列表
     * @param shop_id 书店id 默认全部
     * @param type	  类型 0、全部 1 新书（默认）   2 馆藏书 （如果type为2，shop_id无效，前端变为不可选状态）
     * @param keywords_type  检索条件   0、全部  1、读者证号  2、订单号 3 快递单号 默认 0
     * @param keywords  检索条件
     * @param start_time 发货开始时间   数据格式    2020-05-12 12:00:00
     * @param end_time 发货结束时间     数据格式    2020-05-12 12:00:00
     * @param settle_state		结算状态  0、全部 1 已结算  2 结算中  3 未结算
     * @param way		邮费支付方式  0、全部 1 微信支付 2 系统预算扣除
     * @param page  页数，默认为1，
     * @param limit  条数，默认显示 10条
     */
    public function sponsorPostageList($params)
    {

        $field = $params['field'] ?? null;
        $keywords = $params['keywords'] ?? null;
        $keywords_type = $params['keywords_type'] ?? null;
        $start_time = $params['start_time'] ?? null;
        $end_time = $params['end_time'] ?? null;
        $shop_id = $params['shop_id'] ?? null;
        $shop_all_id = $params['shop_all_id'] ?? null;
        $type = $params['type'] ?? 0;
        $way = $params['way'] ?? '';
        $settle_state = $params['settle_state'] ?? '';
        $select_way = $params['select_way'] ?? '';
        $limit = $params['limit'] ?? 10;
        $page = $params['page'] ?? 10;

        if (empty($field)) {
            $field = ['o.id', 'o.shop_id', 'l.account', 'o.order_number', 'o.price', 'o.payment', 'o.type', 'o.deliver_time', 'o.change_time', 'o.settle_sponsor_time', 'o.settle_affirm_time', 'o.settle_sponsor_manage_id', 'o.settle_affirm_manage_id', 'o.tracking_number', 'o.settle_state'];
        }

        // DB::enableQueryLog();
        $res = $this->from($this->getTable() . ' as o')
            ->select($field)
            ->join('user_account_lib as l', 'l.id', '=', 'o.account_id')
            ->where(function ($query) use ($keywords_type, $keywords) {
                if ($keywords) {
                    if ($keywords_type) {
                        switch ($keywords_type) {
                            case 1:
                                $query->where('l.account', 'like', "%$keywords%");
                                break;
                            case 2:
                                $query->where('o.order_number', 'like', "%$keywords%");
                                break;
                            case 3:
                                $query->where('o.tracking_number', 'like', "%$keywords%");
                                break;
                        }
                    } else {
                        $query->orWhere('l.account', 'like', "%$keywords%")
                            ->orWhere('o.order_number', 'like', "%$keywords%")
                            ->orWhere('o.tracking_number', 'like', "%$keywords%");
                    }
                }
            })

            ->where(function ($query) use ($shop_id, $type, $shop_all_id) {
                if (empty($type)) {
                    if (!empty($shop_id)) {
                        $query->where("o.shop_id", $shop_id)->whereIn('o.shop_id', $shop_all_id);
                    } else if (!empty($shop_all_id)) {
                        // $query->whereRaw("o.shop_id in $shop_all_id or o.shop_id is null");
                        $query->orwhereIn('o.shop_id', $shop_all_id)->orwhere("o.shop_id", null);
                    } else {
                        $query->where('o.shop_id', null);
                    }
                } elseif ($type == 1) {
                    if (!empty($shop_id)) {
                        $query->where("o.shop_id", $shop_id)->whereIn('o.shop_id', $shop_all_id);
                    }
                    $query->where("o.type", 1);
                } else {
                    $query->where("o.type", 2);
                }
            })->where(function ($query) use ($start_time, $end_time, $settle_state, $way) {
                if (!empty($settle_state)) {
                    $query->where("o.settle_state", $settle_state);
                }
                if (!empty($start_time) && !empty($end_time)) {
                    $query->whereBetween('o.deliver_time', [$start_time, $end_time]); //筛选发货时间
                }
                if (!empty($way)) {
                    $query->where('payment', $way);
                }
            })
            ->where('is_pay', 8)
            ->orderByDesc("o.change_time");

        if ($select_way == 1) {
            $res = $res->paginate($limit)->toArray();
        } elseif ($select_way == 2) {
            $res = $res->groupBy('l.account')->get();
            $res = count($res);
        } elseif ($select_way == 3) {
            $res = $res->sum('o.price');
        } elseif ($select_way == 4) {
            $res = $res->count();
        }
        //  dump(DB::getQueryLog());
        if ($select_way == 1) {
            if ($res['data']) {
                $commonControllerObj = new CommonController();
                foreach ($res['data'] as $key => $val) {
                    $res['data'][$key]['pur_manage_name'] = !empty($val['pur_manage_id']) ? Manage::getManageNameByManageId($val['pur_manage_id']) : '';
                    $res['data'][$key]['settle_sponsor_manage_name'] = !empty($val['settle_sponsor_manage_id']) ? Manage::getManageNameByManageId($val['settle_sponsor_manage_id']) : '';
                    $res['data'][$key]['settle_affirm_manage_name'] = !empty($val['settle_affirm_manage_id']) ? Manage::getManageNameByManageId($val['settle_affirm_manage_id']) : '';
                    $res['data'][$key]['return_manage_name'] = !empty($val['return_manage_id']) ? Manage::getManageNameByManageId($val['return_manage_id']) : '';


                    $res['data'][$key][$commonControllerObj->list_index_key] = $commonControllerObj->addSerialNumberOne($key, $page, $limit);
                    $res['data'][$key]['way'] = empty($val['order_id']) ? 2 : 1; //1 为 线上荐购 ， 2 为线下荐购
                }
                $res = $commonControllerObj->disPageData($res);
            }
        }
        return $res;
    }
}
